package ge.gen.eBdgt.db;

import ge.gen.eBdgt.utils.ProjUtils;
import ge.gen.eBdgt.vo.CmuTrackVO;
import ge.gen.eBdgt.vo.IcnVO;
import ge.gen.eBdgt.vo.OutageMonthVO;
import ge.gen.eBdgt.vo.ProjectKindVO;
import ge.gen.eBdgt.vo.TeamWrkLoadVO;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.Cursor;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;

public class DBUtils extends ProjUtils {
	File db;
	private Table icnTable;
	private Table cmuTable;

	public DBUtils(String dbName) {
		db = new File(dbName);
		try {
			icnTable = Database.open(db).getTable("GEN_WORK_LOAD");
			cmuTable = Database.open(db).getTable("CMU_TRACK");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public boolean updateIcnVO(IcnVO vo) throws Exception {
		Map<String, Object> rowMap = translate(vo);
		Cursor c = Cursor.createCursor(icnTable);
		Column column = icnTable.getColumn("ICN");
		if (c.findRow(column, vo.getIcn())) {
			c.deleteCurrentRow();
		}
		icnTable.addRow(icnTable.asRow(rowMap));
		updateCmuTrack(vo.getOrderInfo());
		return true;
	}

	private Map<String, Object> translate(IcnVO vo) {
		LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
		row.put("ICN", vo.getIcn());
		row.put("ICN_2ND", vo.getIcn2nd());
		row.put("CUSTOMER", vo.getCustomer());
		row.put("DES", vo.getDes());
		row.put("DESCRIPTION", vo.getDescription());
		row.put("EE", vo.getEe());
		row.put("GEN_SN", vo.getGenSn());
		row.put("ME", vo.getMe());
		row.put("MOD_DWG", vo.getModDwg());
		row.put("ORDER_DATE", vo.getOrderDate());
		row.put("OUTAGE_DATE", vo.getOutageDate());
		row.put("PM", vo.getPm());
		row.put("REQ_ENG", vo.getReqEng());
		row.put("REQUEST_DATE", vo.getRequestDate());
		row.put("STATION", vo.getStation());
		row.put("STATUS", vo.getStatus());
		row.put("TIMES_NO", vo.getTimesNo());
		row.put("MAIN_WORK_SCOPE_1", vo.getMainWorkScope1());
		row.put("MAIN_WORK_SCOPE_2", vo.getMainWorkScope2());
		row.put("ADITIONAL_SCOPE_1", vo.getAddlScope1());
		row.put("ADITIONAL_SCOPE_2", vo.getAddlScope2());
		row.put("ADITIONAL_SCOPE_3", vo.getAddlScope3());
		row.put("ADITIONAL_SCOPE_4", vo.getAddlScope4());
		row.put("NOTES", vo.getNotes());
		return row;
	}

	public boolean updateCmuTrack(CmuTrackVO vo) throws Exception {
		Map<String, Object> rowMap = getUpdatedCmuTrackVO(vo);
		Cursor c = Cursor.createCursor(cmuTable);
		Column column = cmuTable.getColumn("SHOP_ORDER");
		if (c.findRow(column, vo.getOrderNo())) {
			c.deleteCurrentRow();
		}
		cmuTable.addRow(cmuTable.asRow(rowMap));
		return true;
	}

	private Map<String, Object> getUpdatedCmuTrackVO(CmuTrackVO vo) {
		LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
		row.put("SHOP_ORDER", vo.getOrderNo());
		row.put("END_DATE", vo.getEndDate());
		row.put("MILESTONE_DATE", vo.getMilstnDate());
		row.put("TOTAL_PROJECT_HOURS", vo.getPlanHrs());
		row.put("CURVE_CATEGORY", vo.getCtgryTyp());
		row.put("BUDGET", vo.getBudget());
		return row;
	}

	public boolean saveIcn(IcnVO vo) {
		try {
			updateIcnVO(vo);
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public boolean saveCmuTrack(CmuTrackVO vo) throws Exception {
		return updateCmuTrack(vo);
	}

	public boolean deleteIcn(String icn) {
		try {
			for (Map<String, Object> row : icnTable) {
				String s = (String) row.get("ICN");
				if (s.equalsIgnoreCase(icn))
					icnTable.deleteCurrentRow();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}

	public List getIcnList() {
		List icnList = new ArrayList();
		try {
			for (Map<String, Object> row : icnTable) {
				icnList.add(mapIcnVOForEachRow(row));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Collections.sort(icnList);
		return icnList;
	}

	private IcnVO mapIcnVOForEachRow(Map<String, Object> row) {
		IcnVO icnVO = new IcnVO();
		String orderNo = (String) row.get("TIMES_NO");
		icnVO.setIcn((String) row.get("ICN"));
		icnVO.setIcn2nd((String) row.get("ICN_2ND"));
		icnVO.setCustomer((String) row.get("CUSTOMER"));
		icnVO.setGenSn((String) row.get("GEN_SN"));
		icnVO.setTimesNo(orderNo);
		icnVO.setStation((String) row.get("STATION"));
		icnVO.setEe((String) row.get("EE"));
		icnVO.setMe((String) row.get("ME"));
		icnVO.setOrderDate((String) row.get("ORDER_DATE"));
		icnVO.setRequestDate((String) row.get("REQUEST_DATE"));
		icnVO.setOutageDate((String) row.get("OUTAGE_DATE"));
		icnVO.setStatus((String) row.get("STATUS"));
		icnVO.setDescription((String) row.get("DESCRIPTION"));
		icnVO.setReqEng((String) row.get("REQ_ENG"));
		icnVO.setDes((String) row.get("DES"));
		icnVO.setPm((String) row.get("PM"));
		icnVO.setModDwg((String) row.get("MOD_DWG"));
		icnVO.setMainWorkScope1((String) row.get("MAIN_WORK_SCOPE_1"));
		icnVO.setMainWorkScope2((String) row.get("MAIN_WORK_SCOPE_2"));
		icnVO.setAddlScope2((String) row.get("ADITIONAL_SCOPE_2"));
		icnVO.setAddlScope1((String) row.get("ADITIONAL_SCOPE_1"));
		icnVO.setAddlScope2((String) row.get("ADITIONAL_SCOPE_2"));
		icnVO.setAddlScope3((String) row.get("ADITIONAL_SCOPE_3"));
		icnVO.setAddlScope4((String) row.get("ADITIONAL_SCOPE_4"));
		icnVO.setNotes((String) row.get("NOTES"));
		icnVO.setOrderInfo(getCmuVOInfo(orderNo));
		return icnVO;
	}

	private CmuTrackVO getCmuVOInfo(String orderNo) {
		try {
			Map<String, Object> row = Cursor.findRow(cmuTable, Collections
					.singletonMap("SHOP_ORDER", (Object) orderNo));
			if (row != null) {
				return mapCmuVOForEachRow(row);
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
		return new CmuTrackVO();
	}

	private CmuTrackVO mapCmuVOForEachRow(Map<String, Object> row) {
		CmuTrackVO cmuVO = new CmuTrackVO();
		cmuVO.setOrderNo((String) row.get("SHOP_ORDER"));
		cmuVO.setEndDate((String) row.get("END_DATE"));
		cmuVO.setMilstnDate((String) row.get("MILESTONE_DATE"));
		cmuVO.setPlanHrs((String) row.get("TOTAL_PROJECT_HOURS"));
		cmuVO.setCtgryTyp((String) row.get("CURVE_CATEGORY"));
		cmuVO.setBudget((String) row.get("BUDGET"));
		return cmuVO;
	}

	public IcnVO getIcnVO(String icn) {
		IcnVO voToBeRead = new IcnVO();
		try {
			Map<String, Object> row = Cursor.findRow(icnTable, Collections
					.singletonMap("ICN", (Object) icn));
			if (row != null && row.size() > 0) {
				voToBeRead = mapIcnVOForEachRow(row);
				voToBeRead.setOrderInfo(getCmuTrackVO(voToBeRead.getTimesNo()));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return voToBeRead;
	}

	public CmuTrackVO getCmuTrackVO(Object timesNo) {
		CmuTrackVO voToBeRead = new CmuTrackVO();
		try {
			Map<String, Object> joinRow = Cursor.findRow(cmuTable, Collections
					.singletonMap("SHOP_ORDER", timesNo));
			voToBeRead = mapCmuVOForEachRow(joinRow);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return voToBeRead;
	}

	public List getDescriptionCriteria(String description) {
		List icnList = new ArrayList();
		for (Map<String, Object> row : icnTable) {
			String s = (String) row.get("ADITIONAL_SCOPE_1")
					+ (String) row.get("ADITIONAL_SCOPE_2")
					+ (String) row.get("ADITIONAL_SCOPE_3")
					+ (String) row.get("ADITIONAL_SCOPE_4")
					+ (String) row.get("NOTES");
			if (s.contains(description))
				icnList.add(mapIcnVOForEachRow(row));
		}
		return icnList;
	}

	public List getCmuTrackList() {
		List cmuTrackList = new ArrayList();
		try {
			for (Map<String, Object> row : cmuTable) {
				cmuTrackList.add(mapCmuVOForEachRow(row));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cmuTrackList;
	}

	public List<IcnVO> searchIcnTableByDBField(String search, String dbFieldNm) {
		List icnList = new ArrayList();
		for (Map<String, Object> row : icnTable) {
			String s = (String) row.get(dbFieldNm);
			if (s != null && s.contains(search))
				icnList.add(mapIcnVOForEachRow(row));
		}
		return icnList;
	}

	public List getStatusCriteria(String status) {
		return searchIcnTableByDBField(status, "STATUS");
	}

	public List searchByCustomer(String customer) {
		return searchIcnTableByDBField(customer, "CUSTOMER");
	}

	public List<IcnVO> searchByAddlScope(String addlScope) {
		List icnList = new ArrayList();
		for (Map<String, Object> row : icnTable) {
			String s = (String) row.get("ADITIONAL_SCOPE_1")
					+ (String) row.get("ADITIONAL_SCOPE_2")
					+ (String) row.get("ADITIONAL_SCOPE_3")
					+ (String) row.get("ADITIONAL_SCOPE_4")
					+ (String) row.get("NOTES");
			if (s.contains(addlScope))
				icnList.add(mapIcnVOForEachRow(row));
		}
		return icnList;
	}

	public List<IcnVO> searchByMainScope(String mainWorkScope) {
		List icnList = new ArrayList();
		for (Map<String, Object> row : icnTable) {
			String s = (String) row.get("MAIN_WORK_SCOPE_1")
					+ (String) row.get("MAIN_WORK_SCOPE_2");
			if (s.contains(mainWorkScope))
				icnList.add(mapIcnVOForEachRow(row));
		}
		return icnList;
	}

	public List<IcnVO> searchByReqEngr(String reqEngr) {
		return searchIcnTableByDBField(reqEngr, "REQ_ENG");
	}

	public List<IcnVO> searchBySerNum(String searchValue) {
		return searchIcnTableByDBField(searchValue, "GEN_SN");
	}

	public List<IcnVO> searchByStation(String searchValue) {
		return searchIcnTableByDBField(searchValue, "STATION");
	}

	public List<IcnVO> searchByIcn(String icn) {
		List<IcnVO> icnList = new ArrayList<IcnVO>();
		icn = icn.toUpperCase();
		for (Map<String, Object> row : icnTable) {
			String s = (String) row.get("ICN") + (String) row.get("ICN_2ND");
			s = s.toUpperCase();
			System.out.println(s);
			if (s.contains(icn))
				icnList.add(mapIcnVOForEachRow(row));
		}
		return icnList;
	}

	public List<TeamWrkLoadVO> getTeamWrkLoad() {
		List<TeamWrkLoadVO> teamWrkLoadList = new ArrayList<TeamWrkLoadVO>();
		for (Map<String, Object> row : icnTable) {
			TeamWrkLoadVO vo = new TeamWrkLoadVO();
			vo.setReqEng((String) row.get("REQ_ENG"));
			vo.setIcn((String) row.get("ICN"));
			String stat = (String) row.get("STATUS");
			if (stat != null && stat.startsWith("A")) {
				if (vo.getReqEng() != null
						&& vo.getReqEng().trim().length() > 0)
					teamWrkLoadList.add(vo);
			}
		}
		Collections.sort(teamWrkLoadList);
		return teamWrkLoadList;
	}

	public List<OutageMonthVO> getOutagesByMonth(int startMonth, int year)
			throws Exception {
		List<OutageMonthVO> outagesByMonthList = new ArrayList<OutageMonthVO>();
		for (Map<String, Object> row : icnTable) {
			OutageMonthVO vo = new OutageMonthVO();
			String outDt = (String) row.get("OUTAGE_DATE");
			int monthYear[] = getMonthYear(startMonth, year, outDt);
			if (monthYear != null && monthYear.length > 0
					&& IsMonthWithinYearPeriod(startMonth, year, outDt)) {
				vo.setOutageDate(monthYear[0]);
				vo.setIcn((String) row.get("ICN"));
				outagesByMonthList.add(vo);
			}
		}
		Collections.sort(outagesByMonthList);
		return outagesByMonthList;
	}

	public List<ProjectKindVO> getProjectsByKind(String yr) {
		List<ProjectKindVO> projectsByKindList = new ArrayList<ProjectKindVO>();
		for (Map<String, Object> row : icnTable) {
			ProjectKindVO vo = new ProjectKindVO();
			String mscp1 = (String) row.get("MAIN_WORK_SCOPE_1");
			String mscp2 = (String) row.get("MAIN_WORK_SCOPE_2");
			String dt = (String) row.get("ORDER_DATE");
			if (isNotNull(yr) == false || compareYearWithDate(dt, yr)) {
				if (isNotNull(mscp1)) {
					vo.setProjectKind(mscp1);
					vo.setIcn((String) row.get("ICN"));
					vo.setYear(getYearFromDate(dt));
					projectsByKindList.add(vo);
				}
				if (isNotNull(mscp2)) {
					vo = new ProjectKindVO();
					vo.setProjectKind(mscp2);
					vo.setIcn((String) row.get("ICN"));
					vo.setYear(getYearFromDate(dt));
					projectsByKindList.add(vo);
				}
			}
		}
		Collections.sort(projectsByKindList);
		return projectsByKindList;
	}

	private boolean compareYearWithDate(String s, String yr) {
		if (isNotNull(s)) {
			s = getYearFromDate(s);
			if (s.equals(yr))
				return true;
		}
		return false;
	}

	String getYearFromDate(String s) {
		try {
			if (isNotNull(s)) {
				SimpleDateFormat simpleDateformat = new SimpleDateFormat(
						"M/dd/yyyy");
				Date dt = simpleDateformat.parse(s);
				simpleDateformat = new SimpleDateFormat("yyyy");
				return simpleDateformat.format(dt);
			}
		} catch (ParseException e) {
			return "";
		}
		return "";
	}

	public static ArrayList GetUniqueValues(Collection values) {
		return new ArrayList(new HashSet(values));
	}

	public List<String> getAllYears() {
		List<String> yearList = new ArrayList<String>();
		yearList.add("");
		for (Map<String, Object> row : icnTable) {
			String dt = (String) row.get("ORDER_DATE");
			yearList.add(getYearFromDate(dt));
		}
		return GetUniqueValues(yearList);
	}

	public String getSerialNo(String icn) {
		IcnVO voToBeRead = new IcnVO();
		try {
			Map<String, Object> row = Cursor.findRow(icnTable, Collections
					.singletonMap("ICN", (Object) icn));
			if (row != null && row.size() > 0) {
				return (String) row.get("GEN_SN");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
}
